package com.bbms.dao.impl;

import java.util.HashMap;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.stereotype.Repository;

import com.bbms.dao.BookDAO;
import com.bbms.entity.Book;
import com.bbms.entity.Page;
import com.bbms.util.HibernateUtil;
import com.bbms.util.StringUtil;

/**
 * @author Administrator
 * 图书管理接口实现
 */
@Repository("BookDAOImpl")
public class BookDAOImpl implements BookDAO {

	@Override
	public void save(Book t) {
		HibernateUtil.save(t);
	}

	@Override
	public void update(Book t) {
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE bbms_book SET bbms_book.`status` = " + t.getStatus() + " WHERE bbms_book.id = " + t.getId());
		HibernateUtil.executeSqlUpdate(sb.toString());
	}

	@Override
	public void delete(Book t) {
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE bbms_book SET bbms_book.status = -1, bbms_book.book_logout_time = now() WHERE bbms_book.id = " + t.getId());
		HibernateUtil.executeSqlUpdate(sb.toString());
	}

	/* 图书分页信息(无条件)
	 * @see com.bbms.dao.BaseDAO#getPage(int, int)
	 */
	@Override
	public Page getPage(int currentPage, int pageSize) {
		Session session = HibernateUtil.getSession();
		DetachedCriteria dc = DetachedCriteria.forClass(Book.class);
		Page page = new Page();
		try {
		    
		    Criteria c = dc.getExecutableCriteria(session);
		    // 获得数据总条目
		    Integer rowCount=Integer.parseInt(c.setProjection(Projections.rowCount()).uniqueResult().toString());
		    c.setProjection(null);
		    c.setFirstResult((currentPage-1)*pageSize);
		    c.setMaxResults(pageSize);
		    List<?> list=c.list();
		    page.setPageNo(currentPage);
		    page.setPageSize(pageSize);
		    page.setRows(list);
		    page.setTotal(rowCount);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			HibernateUtil.closeSession(session);
		}
		return page;
	}

	@Override
	public List<?> query() {
		
		return null;
	}
	
	/* 图书分页信息(条件 查询)
	 * @see com.bbms.dao.BookDAO#getPage(int, int, java.lang.String, java.lang.String, java.lang.Integer, java.lang.Integer, java.lang.String, java.lang.String)
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public Page getPage(int currentPage, int pageSize, String s_name, String s_author, Integer s_publisherId,
			Integer s_categoryId, String start_time, String end_time, int authority) {
		StringBuffer sb = new StringBuffer();
		StringBuffer total = new StringBuffer();
		
		sb.append("SELECT * "
				+ "FROM (SELECT b.id, b.`name`, b.author, b.publish_date AS publishDate, b.category_id AS categoryId, "
				+ "c.`name` AS categoryName, b.publisher_id AS publisherId,p.`name` AS publisherName, "
				+ "b.ISBN, b.price, CASE COUNT(b.`status`) + SUM(b.`status`) WHEN 0 THEN -1 ELSE 0 END AS `status` "
				+ "FROM bbms_book AS b LEFT JOIN bbms_book_category AS c ON (c.id = b.category_id) "
				+ "LEFT JOIN bbms_book_publisher AS p ON (p.id = b.publisher_id) "
				+ "WHERE 1 = 1 ");
		
		total.append("SELECT COUNT(id) AS total "
				+ "FROM (SELECT b.id, b.`name`, b.author, b.publish_date AS publishDate, b.category_id AS categoryId, "
				+ "c.`name` AS categoryName, b.publisher_id AS publisherId,p.`name` AS publisherName, "
				+ "b.ISBN, b.price, CASE COUNT(b.`status`) + SUM(b.`status`) WHEN 0 THEN -1 ELSE 0 END AS `status` "
				+ "FROM bbms_book AS b LEFT JOIN bbms_book_category AS c ON (c.id = b.category_id) "
				+ "LEFT JOIN bbms_book_publisher AS p ON (p.id = b.publisher_id) "
				+ "WHERE 1 = 1 ");
				
		if (!StringUtil.isEmpty(s_name)){
			sb.append("AND b.`name` LIKE '%" + s_name + "%' ");
			total.append("AND b.`name` LIKE '%" + s_name + "%' ");
		}
		if (!StringUtil.isEmpty(s_author)){
			sb.append("AND b.author LIKE '%" + s_author + "%' ");
			total.append("AND b.author LIKE '%" + s_author + "%' ");
		}
		if (s_publisherId != null){
			sb.append("AND b.publisher_id = " + s_publisherId + " ");
			total.append("AND b.publisher_id = " + s_publisherId + " ");
		}
		if (s_categoryId != null){
			sb.append("AND b.category_id = " + s_categoryId + " ");
			total.append("AND b.category_id = " + s_categoryId + " ");
		}
		if (StringUtil.isNotEmpty(start_time)){
			sb.append("AND UNIX_TIMESTAMP(b.publish_date) >= UNIX_TIMESTAMP('" + start_time + "') ");
			total.append("AND UNIX_TIMESTAMP(b.publish_date) >= UNIX_TIMESTAMP('" + start_time + "') ");
		}
		if (StringUtil.isNotEmpty(end_time)){
			sb.append("AND UNIX_TIMESTAMP(b.publish_date) <= UNIX_TIMESTAMP('" + end_time + "') ");
			total.append("AND UNIX_TIMESTAMP(b.publish_date) <= UNIX_TIMESTAMP('" + end_time + "') ");
		}
		
		sb.append(" GROUP BY b.ISBN, b.publish_date ) AS temp ");
		total.append(" GROUP BY b.ISBN, b.publish_date ) AS temp ");
		
		if (authority == 0){
			sb.append("WHERE temp.`status` != -1 ");
			total.append("WHERE temp.`status` != -1 ");
		}
		sb.append("LIMIT " + ((currentPage - 1)* pageSize) + "," + pageSize );
		Page page = new Page();
		System.out.println(sb.toString());
		List<?> list = HibernateUtil.executeSqlQuery(sb.toString());
		page.setPageNo(currentPage);
		page.setPageSize(pageSize);
		page.setRows(list);
		System.out.println(total.toString());
		List<HashMap> l = (List<HashMap>) HibernateUtil.executeSqlQuery(total.toString());
		
		page.setTotal(Integer.parseInt(l.get(0).get("total").toString()));
		
		return page;
	}

	/* 图书详细信息
	 * @see com.bbms.dao.BookDAO#getDetailList(java.lang.String, java.lang.String)
	 */
	@Override
	public List<?> getDetailList(String publishDate, String ISBN, int authority) {

		StringBuffer sb = new StringBuffer();
		sb.append("SELECT b.id, b.`name`, b.`status` "
				+ "FROM bbms_book AS b "
				+ "WHERE b.publish_date = '" + publishDate + "' AND b.ISBN = '" + ISBN + "'");
		
		if (authority == 0){
			sb.append(" AND b.`status` > -1");
		} else if (authority == 99) {
			sb.append(" AND b.`status` > 0");
		}
		
		List<?> list = HibernateUtil.executeSqlQuery(sb.toString());
		
		return list;
	}

	/* 更新图书信息(批量)
	 * @see com.bbms.dao.BookDAO#updateBatch(com.bbms.entity.Book)
	 */
	@Override
	public void updateBatch(Book book) {
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE bbms_book AS b "
				+ "SET b.author = '" + book.getAuthor() + "'"
				+ " , b.`name` = '" + book.getName() + "'"
				+ " , b.publisher_id = " + book.getPublisherId()
				+ " , b.category_id = " + book.getCategoryId() 
				+ " , b.ISBN = '" + book.getISBN() + "'"
				+ " , b.publish_date = '" + StringUtil.dateToString(book.getPublishDate()) + "'"
				+ " , b.price = " + book.getPrice() 
				+ " WHERE b.publish_date = '" + StringUtil.dateToString(book.getPublishDate()) + "' AND b.ISBN = '" + book.getISBN() + "'");
		
		HibernateUtil.executeSqlUpdate(sb.toString());
	}

	/* 新增图书信息(批量)
	 * @see com.bbms.dao.BookDAO#addBatch(com.bbms.entity.Book, int)
	 */
	@Override
	public void addBatch(Book book, int booksNum) {
		Session session = HibernateUtil.getSession();
		try{
			System.out.println(booksNum);
			book.setStatus(1);
			for (int i = 0; i <= booksNum - 1; i++){
				Book b = new Book(0, book.getName(), book.getAuthor(), book.getPublisherId(), book.getPublishDate(), book.getCategoryId(), book.getPrice(), book.getISBN(), 1, null, null);
				session.save(b);
				if ( i % 10 == 0 ) {
			        session.flush();
			        session.clear();
			    }
			}
			session.getTransaction().commit();
		} catch(Exception e){
			e.printStackTrace();
			session.getTransaction().rollback();
			throw e;
		}finally {
			HibernateUtil.closeSession(session);
		}
	}

	/* 注销图书(批量)
	 * @see com.bbms.dao.BookDAO#delBatch(com.bbms.entity.Book)
	 */
	@Override
	public void delBatch(Book book) {
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE bbms_book SET bbms_book.status = -1, bbms_book.bbms_book.book_logout_time = now() WHERE bbms_book.publish_date = '" + StringUtil.dateToString(book.getPublishDate()) + "' AND bbms_book.ISBN = '" + book.getISBN()  +"'");
		HibernateUtil.executeSqlUpdate(sb.toString());
	}

}

